package com.songshuai.webapi.crm.service.impl;

import com.alibaba.fastjson.JSON;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.songshuai.webapi.baseapi.constant.BaseConstant;
import com.songshuai.webapi.baseapi.dto.LoginUserDto;
import com.songshuai.webapi.baseapi.dto.feign.response.DictionaryDto;
import com.songshuai.webapi.baseapi.dto.feign.response.MasterSourceItemDto;
import com.songshuai.webapi.baseapi.enumerations.CustomIntendEnum;
import com.songshuai.webapi.baseapi.enumerations.DictionaryTypeEnum;
import com.songshuai.webapi.baseapi.enumerations.FollowStatusEnum;
import com.songshuai.webapi.baseapi.enumerations.ResultStatus;
import com.songshuai.webapi.baseapi.enumerations.crm.error.ClueImportError;
import com.songshuai.webapi.baseapi.pojo.BaseParmDto;
import com.songshuai.webapi.baseapi.pojo.QueryPageRes;
import com.songshuai.webapi.baseapi.pojo.QueryResponse;
import com.songshuai.webapi.baseapi.pojo.feign.request.MasterNumberParamReqDto;
import com.songshuai.webapi.baseapi.utils.AutoGenericUtil;
import com.songshuai.webapi.baseapi.utils.DateUtil;
import com.songshuai.webapi.baseapi.utils.OssUploadUtil;
import com.songshuai.webapi.baseapi.utils.RegexUtil;
import com.songshuai.webapi.crm.constant.ClueImportMsg;
import com.songshuai.webapi.crm.constant.CrmConstant;
import com.songshuai.webapi.crm.constant.CacheKeys;
import com.songshuai.webapi.crm.dao.ClueImportExcelMapper;
import com.songshuai.webapi.crm.dao.CustomInfoMapper;
import com.songshuai.webapi.crm.dto.po.ClueImportBodyPojo;
import com.songshuai.webapi.crm.dto.po.ClueImportExecelPojo;
import com.songshuai.webapi.crm.dto.po.ClueImportHeaderPojo;
import com.songshuai.webapi.crm.dto.po.FollowUserPojo;
import com.songshuai.webapi.crm.dto.request.clueimport.ClueImportReqDto;
import com.songshuai.webapi.crm.dto.response.clueimport.ClueImportResDto;
import com.songshuai.webapi.crm.dto.response.clueimport.ClueImportTemplateDto;
import com.songshuai.webapi.crm.feign.PermissionClient;
import com.songshuai.webapi.crm.model.ClueImportExcel;
import com.songshuai.webapi.crm.service.ClueImportService;
import com.songshuai.webapi.crm.service.DictionaryService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;

@Slf4j
@Service
public class ClueImportServiceImpl extends BaseServiceImpl implements ClueImportService {

    @Resource
    private ClueImportExcelMapper clueImportExcelMapper;

    @Autowired
    private CustomInfoMapper customInfoMapper;

    @Autowired
    private DictionaryService dictionaryService;

    @Autowired
    private PermissionClient permissionClient;

    @Autowired
    private OssUploadUtil uploadUtil;

    @Value("${file.excelTemplatePath}")
    private String excelTemplatePath;

    @Value("${file.clueExportTemplateName}")
    private String clueExportTemplateName;

    private String emptyStr="";

    public QueryResponse uploadData(MultipartFile file) throws IOException {
        LoginUserDto currentUser=super.getCurrentUser();//当前登录人
        if(null==currentUser){
            //没有登录人信息
            return new QueryResponse(ResultStatus.NO_LOGIN);
        }
        //校验文件名、文件格式、文件大小等信息
        QueryResponse validateQr= validateFileInfo(file);
        if(null!=validateQr){
            //校验结果为空，则说明校验通过。
            //不为空，返回校验结果
            return validateQr;
        }
        //获取上传文件里,第一个sheet
        Sheet sheet=acquireSheet(file);
        //遍历sheet的所有单元格，读取导入线索集合
        List<ClueImportBodyPojo> clueImportBodyPojoList= traversalAllCell(sheet);
        if(CollectionUtils.isEmpty(clueImportBodyPojoList)){
            return new QueryResponse(ResultStatus.UPLOAD_FILE_NO_RECORD__ERROR);
        }
        //业务校验
        Map<String,Object> validateResultMap=businessValidate(clueImportBodyPojoList,currentUser);
        //已校验的线索信息，暂存Redis
        tempSaveCache(validateResultMap,file.getOriginalFilename());
        //将校验结果Msg设置到响应DTO中
        Object validateMsgObj=validateResultMap.get("validateMsgList");
        ClueImportResDto clueImportResDto=new ClueImportResDto();
        clueImportResDto.setValidateMsgList((List<StringBuffer>)validateMsgObj);
        clueImportResDto.setPassRowNum((Integer) validateResultMap.get("passRowNum"));
        clueImportResDto.setFailRowNum((Integer) validateResultMap.get("failRowNum"));
        clueImportResDto.setTotalRowNum((Integer) validateResultMap.get("totalRowNum"));
        return  QueryResponse.setResData(clueImportResDto);
    }

    /**
     * 校验文件名、文件格式，文件大小
     * @param file
     * @return
     * @throws IOException
     */
    public QueryResponse validateFileInfo(MultipartFile file) throws IOException {
        if (null==file)
        {
            return new QueryResponse(ResultStatus.UPLOAD_FILE_NO_EXIST__ERROR);
        }
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return new QueryResponse(ResultStatus.UPLOAD_FILE_SUFFIX__ERROR);
        }
        //校验文件上传Excel文件，是否超过1M
        long sizeByte=file.getSize();
        int maxKb= CrmConstant.CLUE_UPLOAD_MAX_KB;
        long sizeKb=sizeByte/maxKb;
        if(sizeKb>maxKb){
            return new QueryResponse(ResultStatus.UPLOAD_FILE_TOO_BIG);
        }
        //获取上传文件里,第一个sheet
        Sheet sheet=acquireSheet(file);
        if(null==sheet){
            return new QueryResponse(ResultStatus.UPLOAD_FILE_FORMAT__ERROR);
        }
        return null;
    }

    /**
     * 1、业务校验 校验姓名、手机号 是否为空；校验手机号是否已存在等信息
     * 2、校验通过和失败的线索信息，分别放置在不同的List中
     * @param clueImportExcelList
     * @return  Map
     */
    Map<String,Object> businessValidate(List<ClueImportBodyPojo> clueImportExcelList,LoginUserDto currentUser) {
        Map<String,Object> validateResult=new HashMap<>();
        //通过Feign调用Base-api接口，获取字典信息【跟进状态、意向级别、学段、来源、性别】
        List<DictionaryDto> followStatusDictList = dictionaryService.queryDictListByType(DictionaryTypeEnum.FollowStatus.getCode());
        List<DictionaryDto> intendDictList = dictionaryService.queryDictListByType(DictionaryTypeEnum.IntendLevel.getCode());
        List<DictionaryDto> stageDictList = dictionaryService.queryDictListByType(DictionaryTypeEnum.Stage.getCode());
        List<MasterSourceItemDto> sourceItemDtoList = dictionaryService.querySourceList();
        List<DictionaryDto> sexDictList = dictionaryService.queryDictListByType(DictionaryTypeEnum.Sex.getCode());
        //List<DictionaryDto> gradeDictList = dictionaryService.queryDictListByType(DictionaryTypeEnum.Sex.getCode());

        List<FollowUserPojo> followUserList=getFollowUserList(currentUser.getCampusId());
        //遍历List 获取所有的手机号
        List<String> phoneList = traversalPhoneList(clueImportExcelList);
        Set<String> phoneSet = new HashSet<>(phoneList);
        //查询哪些手机号，在现有线索信息表已经存在
        Set<String> existPhoneSet = null;
        if (!CollectionUtils.isEmpty(phoneSet)) {
            existPhoneSet = clueImportExcelMapper.queryExistPhoneSet(phoneSet);
        }
        boolean existPhoneFlag = false;
        if (!CollectionUtils.isEmpty(existPhoneSet)) {
            existPhoneFlag = true;
        }

        List<ClueImportBodyPojo> cluePassList=new ArrayList<>();
        List<ClueImportBodyPojo> clueFailList=new ArrayList<>();
        ClueImportBodyPojo clueImportExcelItem = null;
        ClueImportBodyPojo clueValidateResult = null;
        List<StringBuffer> validateMsgList=new ArrayList<>();
        //StringBuffer validateMsg = new StringBuffer();
        //校验失败信息 默认值0  1：手机号在线索信息表已经存在  2：手机号重复
        int errorResult=0;
        int rowNum=0;
        int listIndexMax=clueImportExcelList.size()-1;
        //遍历clueImportExcelList集合，逐个校验元素
        for (int i = 0; i <= listIndexMax; i++) {
           clueValidateResult =new ClueImportBodyPojo();
            errorResult=0;
            rowNum=i+3;
            StringBuffer validateMsgRow = new StringBuffer();
            clueImportExcelItem = clueImportExcelList.get(i);
            //姓名
            String customName = clueImportExcelItem.getCustomName();
            if (StringUtils.isEmpty(customName)) {
                validateMsgRow=validateMsgRow.append(ClueImportMsg.emptyNameTip);
            } else {
                clueValidateResult.setCustomName(customName);
            }
            //手机号
            String phone = clueImportExcelItem.getPhone();
            if (StringUtils.isEmpty(phone)) {
                validateMsgRow=validateMsgRow.append(ClueImportMsg.emptyPhoneTip);
            } else {
                if(phone.length()!=11){
                    //手机号请保持11位长度，不足请在后面补0
                    //phone=AutoGenericUtil.addZeroForNum(phone,11);
                    errorResult= ClueImportError.ERROR_PHONE_FORMAT.getCode();  //手机号格式不合法
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.phoneLengthErrorTip,phone));
                }else if(!RegexUtil.isMobile(phone)){
                    //校验手机格式
                    errorResult= ClueImportError.ERROR_PHONE_FORMAT.getCode();  //手机号格式不合法
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.phoneErrorTip,phone));
                }else if (true == existPhoneFlag && existPhoneSet.contains(phone)) {
                    //手机号在线索信息表已经存在
                    errorResult=ClueImportError.ERROR_PHONE_IS_EXIST.getCode();
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.phoneExistTip,customName,phone));
                } else if (validatePhoneRepeat(phoneList, phone, i)) {
                    //手机号在Excel中存在重复
                    errorResult=ClueImportError.ERROR_PHONE_REPEAT.getCode();
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.phoneRepeatTip,customName,phone,getFirstLine(phoneList, phone)));
                }
            }
            clueValidateResult.setPhone(phone);

            //性别
            String sexStr = clueImportExcelItem.getSexStr();
            clueValidateResult.setSexStr(sexStr);
            if (!StringUtils.isEmpty(sexStr)) {
                clueValidateResult.setSex(dictionaryService.queryDictIdFromList(sexStr, sexDictList));
            }

            //备用手机号
            if (StringUtils.isNotEmpty(clueImportExcelItem.getPhoneBackup())) {
                if (clueImportExcelItem.getPhoneBackup().length() != 11) {
                    //备用手机号请保持11位长度，不足请在后面补0
                    //clueImportExcelItem.setPhoneBackup(AutoGenericUtil.addZeroForNum(clueImportExcelItem.getPhoneBackup(), 11));
                    validateMsgRow = validateMsgRow.append(String.format(ClueImportMsg.phoneBackupLengthErrorTip, clueImportExcelItem.getPhoneBackup()));
                }else if (!RegexUtil.isMobile(clueImportExcelItem.getPhoneBackup())) {
                    //校验手机格式
                    // errorResult= ClueImportError.ERROR_PHONE_FORMAT.getCode();  //手机号格式不合法
                    validateMsgRow = validateMsgRow.append(String.format(ClueImportMsg.phoneBackupErrorTip, clueImportExcelItem.getPhoneBackup()));
                }else{
                    //备用手机号合法 ，可导入。
                    clueValidateResult.setPhoneBackup(clueImportExcelItem.getPhoneBackup());
                }
            }

            //出生日期
            String birthday = clueImportExcelItem.getBirthdayStr();
            clueValidateResult.setBirthdayStr(birthday);
            if (!StringUtils.isEmpty(birthday)) {
                try {
                    if(DateUtil.isDateStr(birthday,DateUtil.dateFormat)||DateUtil.isDateStr(birthday,DateUtil.dateFormat1)){
                        clueValidateResult.setBirthday(DateUtil.ChangeStringToDate(birthday, DateUtil.dateFormat));
                    }else{
                        validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.dateErrorTip,birthday));
                    }
                } catch (Exception e) {
                    log.error(ClueImportError.ERROR_DATE_FORMAT.getMsg(),e);
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.dateErrorTip,birthday));
                }
            }
            //学校
            clueValidateResult.setSchool(clueImportExcelItem.getSchool());
            //阶段
            clueValidateResult.setStageStr(clueImportExcelItem.getStageStr());
            if (!StringUtils.isEmpty(clueImportExcelItem.getStageStr())) {
                clueValidateResult.setStageStr(clueImportExcelItem.getStageStr());
                //获取学段ID
                Integer stageId = dictionaryService.queryDictIdFromList(clueImportExcelItem.getStageStr(), stageDictList);
                if (null != stageId) {
                    clueValidateResult.setStage(stageId);
                } else {
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.stageErrorTip,clueImportExcelItem.getStageStr()));
                }
            }
            //年级名字
            clueValidateResult.setGradeName(clueImportExcelItem.getGradeName());

            //家庭住址
            clueValidateResult.setHomeAddress(clueImportExcelItem.getHomeAddress());
            //来源
            String source = clueImportExcelItem.getSource();
            clueValidateResult.setSource(source);
            if (!StringUtils.isEmpty(source)) {
                //来源ID
                Integer sourceId = dictionaryService.querySourceIdFromList(source, sourceItemDtoList);
                if (null != sourceId) {
                    clueValidateResult.setSourceCode(sourceId);
                } else {
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.sourceErrorTip,source));
                }
            }
            //跟进状态
            String followStatus = clueImportExcelItem.getFollowStatusStr();
            clueValidateResult.setFollowStatusStr(followStatus);
            if (!StringUtils.isEmpty(followStatus)) {
                //跟进状态ID
                Integer followStatusId=dictionaryService.queryDictIdFromList(followStatus,followStatusDictList);
                if(null!=followStatusId){
                    clueValidateResult.setFollowStatus(followStatusId);
                }else{
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.followStatusErrorTip,followStatus));
                    clueValidateResult.setFollowStatus(FollowStatusEnum.WaitFollow.getCode());//默认显示为“待跟进”
                }
            }else{
                clueValidateResult.setFollowStatus(FollowStatusEnum.WaitFollow.getCode());//默认显示为“待跟进”
            }

            //意向级别
            String intention = clueImportExcelItem.getIntendStr();
            clueValidateResult.setIntendStr(intention);
            if(!StringUtils.isEmpty(intention)){
                //设置意向级别ID
                Integer intendId=dictionaryService.queryDictIdFromList(intention,intendDictList);
                if(null!=intendId){
                    clueValidateResult.setIntend(intendId);
                }else {
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.intendErrorTip,intention));
                    clueValidateResult.setIntend(CustomIntendEnum.Mid.getCode());
                }
            }else{
                clueValidateResult.setIntend(CustomIntendEnum.Mid.getCode());
            }

            //备注
            clueValidateResult.setRemark(clueImportExcelItem.getRemark());
            //跟进人
            if(StringUtils.isNotEmpty(clueImportExcelItem.getFollowUser())){
                clueValidateResult.setFollowUser(clueImportExcelItem.getFollowUser());
                Integer followUserId = validateFollowUser(followUserList,clueImportExcelItem.getFollowUser());
                if(null!=followUserId){
                    clueValidateResult.setFollowUserId(followUserId);
                }else{
                    validateMsgRow=validateMsgRow.append(String.format(ClueImportMsg.followUserErrorTip,clueImportExcelItem.getFollowUser()));
                }
            }
            if(validateMsgRow.length()>0){
                validateMsgRow=new StringBuffer(String.format(ClueImportMsg.rowNumTip,String.valueOf(rowNum))).append(validateMsgRow);
                validateMsgList.add(validateMsgRow);
                clueValidateResult.setImportMessage(validateMsgRow.toString());
            }
            //ID
            clueValidateResult.setId(snowFlakeUtil.nextId());
            //学号
            if(i==0||i==listIndexMax){
                clueValidateResult.setCustomNo(generateCustomNo(currentUser,true));
            }else{
                clueValidateResult.setCustomNo(generateCustomNo(currentUser,false));
            }
            if(StringUtils.isEmpty(clueImportExcelItem.getCustomName())||StringUtils.isEmpty(clueImportExcelItem.getPhone())){
                //手机号为空或学生姓名为空，学生不可以正常导入
                //导入结果是失败
                clueValidateResult.setImportResult(CrmConstant.CLUE_UPLOAD_FAIL);
                clueFailList.add(clueValidateResult);
            }else if(errorResult!=0){
                //手机号校验失败
                clueValidateResult.setImportResult(CrmConstant.CLUE_UPLOAD_FAIL);
                clueFailList.add(clueValidateResult);
            }else {
                //学生可以正常导入
                //导入结果是成功
                clueValidateResult.setImportResult(CrmConstant.CLUE_UPLOAD_PASS);
                cluePassList.add(clueValidateResult);
            }
        //for循环结束
        }
        validateResult.put("validateMsgList",validateMsgList);   //校验结果msg
        validateResult.put("cluePassList",cluePassList);            //校验通过的线索
        validateResult.put("clueFailList",clueFailList);            //校验失败的线索
        validateResult.put("passRowNum",cluePassList.size());            //校验通过的条数
        validateResult.put("failRowNum",clueFailList.size());            //校验失败的条数
        validateResult.put("totalRowNum",cluePassList.size()+clueFailList.size());            //总条数
        return validateResult;
    }

    /**
     * 校验手机号phone，是否多次存在phoneList，是否是第一个
     * @param phoneList
     * @param indexNum
     * @return true:该手机号在List中存在多个，并且不是第一个。 false:没有存在多个，或者重复，但该手机号是第一个
     */
    boolean validatePhoneRepeat(List<String> phoneList,String phone,int indexNum){
        if(CollectionUtils.isEmpty(phoneList)|| StringUtils.isEmpty(phone)){
            return false;
        }
        int indexFirst=phoneList.indexOf(phone);
        int indexLast=phoneList.lastIndexOf(phone);
        if(indexFirst!=indexLast){
            //集合中，该手机号的index位置，
            //如果第一个和最后一个不相等，就认为有重复
            if(indexFirst!=indexNum){
                //重复，且不是第一个
                return true;
            }
        }
        return false;
    }

    /**
     * 获取重复手机号的第一次出现的行号
     * @param phoneList
     * @param phone
     * @return
     */
    String getFirstLine(List<String> phoneList,String phone){
        if(CollectionUtils.isEmpty(phoneList)|| StringUtils.isEmpty(phone)){
            return CrmConstant.ZERO;
        }
        int indexFirst=phoneList.indexOf(phone);
        //3:导入模板前两行是说明和title,然后索引是从0开始。故由索引index计算行号,需要加3
        return String.valueOf(indexFirst+3);
    }

    /**
     * 获取上传文件里,第一个sheet
     * @param file
     * @return
     * @throws IOException
     */
    Sheet acquireSheet(MultipartFile file) throws IOException {
        String fileName = file.getOriginalFilename();
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        return sheet;
    }

    /**
     *  遍历List 获取所有的手机号
     * @param clueImportExcelList
     * @return
     */
    List<String> traversalPhoneList(List<ClueImportBodyPojo> clueImportExcelList){
        List<String> allPhoneList=new ArrayList<>();
        //取出所有的手机号
        for (ClueImportBodyPojo clueImportExcel:clueImportExcelList) {
            allPhoneList.add(clueImportExcel.getPhone());
        }
        return allPhoneList;
    }

    /**
     *  遍历sheet的所有单元格，读取导入线索集合
     * @param sheet
     * @return
     */
    List<ClueImportBodyPojo> traversalAllCell(Sheet sheet) {
        List<ClueImportBodyPojo> clueImportExcelList=new ArrayList<>();
        ClueImportBodyPojo clueImportExcel = null;
        //r=2: 因为线索导入EXCEL模板，前两行不是线索数据，而是说明和titile
        //遍历Excel,取出所有的单元格元素
        for (int r = 2; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (null == row) {
                continue;
            }
            clueImportExcel = new ClueImportBodyPojo();
            //姓名
            Cell nameCell = row.getCell(0);
            clueImportExcel.setCustomName(getCellValue(nameCell));
            //手机号
            Cell phoneCell = row.getCell(1);
            clueImportExcel.setPhone(getCellValue(phoneCell));
            //性别
            Cell sexCell = row.getCell(2);
            clueImportExcel.setSexStr(getCellValue(sexCell));
            //备用手机号
            Cell phoneBackupCell = row.getCell(3);
            clueImportExcel.setPhoneBackup(getCellValue(phoneBackupCell));
            //出生日期
            Cell birthdayCell = row.getCell(4);
            clueImportExcel.setBirthdayStr(getCellValue(birthdayCell));
            //学校
            Cell schoolCell = row.getCell(5);
            clueImportExcel.setSchool(getCellValue(schoolCell));
            //阶段
            Cell stageCell = row.getCell(6);
            clueImportExcel.setStageStr(getCellValue(stageCell));
            //年级
            Cell gradeCell = row.getCell(7);
            clueImportExcel.setGradeName(getCellValue(gradeCell));
            //家庭住址
            Cell homeAddressCell = row.getCell(8);
            clueImportExcel.setHomeAddress(getCellValue(homeAddressCell));
            //来源
            Cell sourceCell = row.getCell(9);
            clueImportExcel.setSource(getCellValue(sourceCell));
            //跟进状态
            Cell followStatusCell = row.getCell(10);
            clueImportExcel.setFollowStatusStr(getCellValue(followStatusCell));
            //意向级别
            Cell intendCell = row.getCell(11);
            clueImportExcel.setIntendStr(getCellValue(intendCell));
            //备注
            Cell remarkCell = row.getCell(12);
            clueImportExcel.setRemark(getCellValue(remarkCell));
            //跟进人
            Cell followUserCell = row.getCell(13);
            clueImportExcel.setFollowUser(getCellValue(followUserCell));

            //校验空行,校验clueImportExcel中所有属性是否都为空
            if(validateNullRow(clueImportExcel)){
                //不为空，加入返回结果集中
                clueImportExcelList.add(clueImportExcel);
            }
        }
        return clueImportExcelList;
    }

    /**
     * 校验空行
     * @param clueImportExcel
     * @return true:空行 false:非空
     */
    boolean validateNullRow(ClueImportBodyPojo clueImportExcel ){
        if(null==clueImportExcel){
            return false;
        }
        boolean  flag =  false ;
        //java反射机制判断对象所有属性是否全部为空
        for (Field f : clueImportExcel.getClass().getDeclaredFields()) {
            //遍历属性
            f.setAccessible( true );
            //设置属性是可以访问的(私有的也可以)
            Object val = null;//执行get方法返回一个Object
            //通过反射获取bean对象相应字段Field的值
            PropertyDescriptor pd = null;
            try {
                pd = new PropertyDescriptor(f.getName(), clueImportExcel.getClass());
                Method method = pd.getReadMethod();//获得get方法
                val = method.invoke(clueImportExcel);
            } catch (Exception e) {
                log.error(ClueImportError.ERROR_VALIDATE_NULL_ROW.getMsg(), e);
                continue;
            }
            if (val!= null&&StringUtils.isNotBlank(val.toString()) ) {
                //只要有1个属性不为空,那么就是非空
                flag =  true ;
                break ;
            }
        }
        return  flag;
    }

    /**
     * 获取单元格中的值，所有值都返回字符串类型
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        String strCell = emptyStr;
        if (cell == null) {
            return strCell;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    strCell =DateUtil.ChangeDateToString(date,DateUtil.dateFormat);
                    break;
                }
                // 不是日期格式，则防止当数字过长时以科学计数法显示
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                strCell = cell.toString();
                break;
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return strCell;
    }

    private String getDateCellValue(Cell cell) {
        String strCell = emptyStr;
        if (cell == null) {
            return strCell;
        }
        if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
            Double birthdayDouble=cell.getNumericCellValue();
            //Date date =DateUtil.rolAfterDay(DateUtil.date1990,birthdayDouble.intValue());
            //strCell =DateUtil.ChangeDateToString(date,DateUtil.dateFormat);
            return String.valueOf(birthdayDouble.intValue());
        }
        Date date = cell.getDateCellValue();
        strCell =DateUtil.ChangeDateToString(date,DateUtil.dateFormat);
        return strCell;
    }



    public String generateCustomNo(LoginUserDto loginUser,boolean firstLastFlag){
        //学员编号
        String campusId= AutoGenericUtil.autoGenericCode(loginUser.getCampusId(),5);
        StringBuffer customNoBuffer =new StringBuffer();
        String customNoRedisKey = CrmConstant.CUSTOME_NO_SIGN + campusId;
        if(firstLastFlag){
            //导入线索EXCEL,第一次和最后一次获取学号，走Feign
            MasterNumberParamReqDto paramReqDto = new MasterNumberParamReqDto();
            paramReqDto.setLoginId(loginUser.getId());
            paramReqDto.setNumberKey(CrmConstant.CUSTOME_NO_SIGN);
            paramReqDto.setNumberType(campusId);
            String customNo = schoolMasterNumberClient.queryNumberValueByParam(paramReqDto).getData();
           // return customNoBuffer.append(CrmConstant.CUSTOME_NO_SIGN).append(campusId).append(customNo).toString();
            return customNo;
        }else{
            if(redisUtil.hasKey(customNoRedisKey)){//缓存中key存在
                //直接走redis
                Long result=redisUtil.incr(customNoRedisKey,1);
                String customNo = AutoGenericUtil.autoGenericCode(result,BaseConstant.DEFAULT_INIT_LENGTH);
                return customNoBuffer.append(CrmConstant.CUSTOME_NO_SIGN).append(campusId).append(customNo).toString();
            }else{//缓存中不存在，走Feign接口
                return generateCustomNo(loginUser,true);
            }
        }


    }

    public String generateBatchNo(LoginUserDto loginUser){
        //批次号
        MasterNumberParamReqDto paramReqDto = new MasterNumberParamReqDto();
        paramReqDto.setLoginId(loginUser.getId());
        paramReqDto.setNumberKey(CrmConstant.CUSTOME_NO_SIGN);
        paramReqDto.setNumberType(AutoGenericUtil.autoGenericCode(loginUser.getCampusId(),5));
        String batchNo = schoolMasterNumberClient.queryNumberValueByParam(paramReqDto).getData();
        //batchNo = CrmConstant.CUSTOM_BATCH_NO + AutoGenericUtil.autoGenericCode(loginUser.getCampusId(),5) + batchNo;
        return batchNo;
    }

    /**
     * 跟进校区ID，获取跟进人
     * @param campusId
     * @return
     */
    public  List<FollowUserPojo> getFollowUserList(Integer campusId){
        QueryResponse qr=permissionClient.getUsersByCampusId(getBearerToken(),campusId,true);
        FollowUserPojo followUserPo = null;
        List<FollowUserPojo> lists = new ArrayList();
        String userIdKey="user_id";
        String userNameKey="true_name";
        if(qr.getData()!=null){
            for(Map itemMap:(List<Map>)qr.getData()){
                followUserPo = new FollowUserPojo();
                followUserPo.setUserId(Integer.parseInt(itemMap.get(userIdKey).toString()));
                followUserPo.setUserName(itemMap.get(userNameKey).toString());
                lists.add(followUserPo);
            }
        }
        return lists;
    }

    /**
     * 从跟进人集合中，获取跟进人名字对应的ID
     * @param followUserList
     * @param followUserName
     * @return
     */
    public Integer validateFollowUser(List<FollowUserPojo> followUserList,String followUserName){
       if(CollectionUtils.isEmpty(followUserList)||StringUtils.isEmpty(followUserName)){
            return null;
       }
      for(  FollowUserPojo followUserPo :followUserList){
          if(followUserName.equals(followUserPo.getUserName())){
              return followUserPo.getUserId();
          }
      }
        return null;
    }



    /**
     * 已校验的线索信息，暂存Redis
     * @param validateResultMap 已校验的线索信息
     * @param fileName 上传excel的文件名
     * @return
     */
    boolean tempSaveCache(Map<String,Object> validateResultMap,String fileName){
        Integer currentUserId= super.getCurrentUserId();        //当前登录人ID
        ClueImportHeaderPojo clueImportHeader=new ClueImportHeaderPojo();
        clueImportHeader.setFileName(fileName);
        clueImportHeader.setCreateBy(currentUserId);
        clueImportHeader.setUpdateBy(currentUserId);
        String clueImportInfoCacheKey=currentUserId+ CacheKeys.SEPARATOR+ CacheKeys.CLUE_IMPORT_INFO_JSON_STR;
        //已校验的线索信息，暂存Redis，时效TIME_OUT_1：10分钟
        Object cluePassListObj=validateResultMap.get("cluePassList");
        Object clueFailListObj=validateResultMap.get("clueFailList");
        if(null==cluePassListObj&&null==clueFailListObj){
            return true;
        }
        List<ClueImportBodyPojo> cluePassList=(List<ClueImportBodyPojo>)cluePassListObj;
        List<ClueImportBodyPojo> clueFailList=(List<ClueImportBodyPojo>)clueFailListObj;
        ClueImportExecelPojo clueImportExecelPojo=new ClueImportExecelPojo(clueImportHeader, cluePassList,clueFailList);
        String clueImportInfoJsonStr=JSON.toJSON(clueImportExecelPojo).toString();
        boolean saveFlag=redisUtil.set(CacheKeys.CLUE_IMPORT_INFO_JSON_STR,clueImportInfoJsonStr, CacheKeys.TIME_OUT_1);
        if(saveFlag){
            return true;
        }
        return false;
    }

    /**
     * 将导入的线索信息，持久化到数据库
     * @return
     */
    @Override
    @Transactional
    public QueryResponse persistData() {
        LoginUserDto currentUser=super.getCurrentUser();//当前登录人
        if(null==currentUser){
            return new QueryResponse(ResultStatus.NO_LOGIN);
        }
        String clueImportInfoCacheKey= currentUser.getId()+ CacheKeys.SEPARATOR+ CacheKeys.CLUE_IMPORT_INFO_JSON_STR;
        //从缓存中获取线索导入信息
        String clueImportInfoJsonStr=redisUtil.getString(clueImportInfoCacheKey);
        if(StringUtils.isEmpty(clueImportInfoJsonStr)){
            return new QueryResponse(ResultStatus.UPLOAD_DATA_NO_EXIST_ERROR);
        }
        //线索导入信息JSON字符串，转为POJO
        ClueImportExecelPojo clueImportExecelPojo=JSON.parseObject(clueImportInfoJsonStr,ClueImportExecelPojo.class);
        ClueImportHeaderPojo clueImportHeaderPojo=clueImportExecelPojo.getClueImportHeaderPojo();
        clueImportHeaderPojo.setBatchNo(generateBatchNo(currentUser));
        Date date =new Date();
        clueImportHeaderPojo.setCreateTime(date);
        clueImportHeaderPojo.setUpdateTime(date);
        //导入成功的信息
        List<ClueImportBodyPojo> cluePassList=clueImportExecelPojo.getCluePassList();
        if(!CollectionUtils.isEmpty(cluePassList)){
            clueImportExcelMapper.insertBatch(cluePassList,clueImportHeaderPojo);
            //将导入成功的信息保存到线索信息表
            customInfoMapper .insertBatch(cluePassList,clueImportHeaderPojo);
        }
        //导入失败的信息
        List<ClueImportBodyPojo> clueFailList=clueImportExecelPojo.getClueFailList();
        if(!CollectionUtils.isEmpty(clueFailList)){
            clueImportExcelMapper.insertBatch(clueFailList,clueImportHeaderPojo);
        }

        //持久化到数据库后，删除相应redis缓存。
        redisUtil.del(clueImportInfoCacheKey);
        return new QueryResponse();
    }

    /**
     * 查询线索列表
     * @param parmDto
     * @return
     */
    public QueryResponse queryClueImportList(BaseParmDto<ClueImportReqDto> parmDto) {
        Page<Object> page = PageHelper.startPage(parmDto.getPageIndex(), parmDto.getPageSize());
        List<ClueImportExcel>  clueImportExcelList=clueImportExcelMapper.queryClueImportList();
        return QueryPageRes.setQueryResponseData(clueImportExcelList, page.getTotal());
    }

    /**
     * 获取模板下载地址
     * @return
     */
    public QueryResponse getTemplateDownloadUrl(){
        String filePathName=excelTemplatePath+clueExportTemplateName;
        ClueImportTemplateDto clueImportTemplateDto=new ClueImportTemplateDto( uploadUtil.getOSSUrl(filePathName,CrmConstant.CLUE_UPLOAD_TEMPLATE_VALID_TIME));
        return QueryResponse.setResData(clueImportTemplateDto);
    }

}
